package dataprocessing;

import java.awt.List;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.SortedMap;
import java.util.SortedSet;
import java.util.TimeZone;
import java.util.TreeSet;

import db.*;

public class DBProcessor {

	DBConnection db = new DBConnection();
	
	public void processArtistSuggestions2(int start, int end){

		if(end == 0)
			end = start+ 1 * 50000000 *2;
		try{
			File f1 = new File("data/recommendations_optimized.tsv");
			if(!f1.exists()){
			  f1.createNewFile();
			}
			else{
				f1.delete();
				f1.createNewFile();
			}
			  
			InputStreamReader isr = new InputStreamReader(new FileInputStream("data/recommendations.tsv"), "UTF-8" );
			BufferedReader reader = new BufferedReader(isr);
	        
	        Writer writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(f1),"UTF8"));
	        
	        SortedSet<Rec> set = new TreeSet<Rec>();
	        //... Loop as long as there are input lines.
	        String line = null;
	        int count = 1;
	        int art1 = 1;
        	int art2 = 1;
        	int prev_art1 = 1;
	        //   0       1        2     3      4      5       6         7      8
	        //userid-timestamp-artid-artname-traid-traname-artist_id-song_id-user_id
	        while ((line=reader.readLine()) != null) {

	        	String[] lineArr = line.split("\t");
	        	if(lineArr.length != 2)
	        		continue;
	        	prev_art1 = art1;
	        	art1 = Integer.parseInt(lineArr[0]);
	        	art2 = Integer.parseInt(lineArr[1]);
	        	
	        	if(prev_art1 != art1){
	        		int size  = set.size();
	        		int count2 = 1;
	    	        Iterator it = set.iterator();
	    	        System.out.println("size=" + size);
	    	        while (it.hasNext()) {
	    	            // Get element
	    	            Rec element = (Rec) it.next();
	    	            if(count2 % 1000 == 0)
	    	            	System.out.println(element + "/" + size);
	    	            writer.write(element.artist1Id + "\t" + element.artist2Id + "\n");
	    	            count++;
	    	        }
	    	        set.clear();	        		
	        	}
	        	set.add(new Rec(art1, art2));
	        	
	        	if(count % 1000 == 0)
	        		System.out.println(count + "\t\t\t"+art1+"\n");
	        	count++;
	        }
	        reader.close();  // Close to unlock
	        
	        //... Close reader and writer.
	        
	        writer.close();  // Close to unlock and flush to disk.    
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

	}
	
	public void processArtistSuggestions(){

		db.connectToDB();
		Connection conn = db.getConnection();
		PreparedStatement prep = null;
		
		try {
			prep = conn.prepareStatement("SELECT artist_id, user_id FROM artists_users");
			ResultSet r = prep.executeQuery();
			
			HashMap<Integer, ArrayList<Integer>> artists_users = new HashMap<Integer, ArrayList<Integer>>(200000);
			HashMap<Integer, ArrayList<Integer>> users_artists = new HashMap<Integer, ArrayList<Integer>>(400000);
			while (r.next()) {
				int artistID = r.getInt("artist_id");
				int userID = r.getInt("user_id");
				if(artists_users.containsKey(artistID)){
					ArrayList<Integer> arr = artists_users.get(artistID);
					arr.add(userID);
				}
				else{
					ArrayList<Integer> arr = new ArrayList<Integer>();
					arr.add(userID);
					artists_users.put(artistID, arr);
				}
				
				if(users_artists.containsKey(userID)){
					ArrayList<Integer> arr = users_artists.get(userID);
					arr.add(artistID);
				}
				else{
					ArrayList<Integer> arr = new ArrayList<Integer>();
					arr.add(artistID);
					users_artists.put(userID, arr);
				}
			}

			r.close();
			prep.close();
			File f1 = new File("data/recommendations.tsv");
			if(!f1.exists()){
			  f1.createNewFile();
			}
			else{
				f1.delete();
				f1.createNewFile();
			}
	        
	        Writer writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(f1),"UTF8"));

	        
			Iterator it = artists_users.entrySet().iterator();
			int count = 0;
			while (it.hasNext()) {
				Map.Entry pairs = (Map.Entry) it.next();
				//System.out.println(pairs.getKey() + " = " + pairs.getValue());
				ArrayList<Integer> users = (ArrayList<Integer>) pairs.getValue();
				for (Integer integer : users) {
					ArrayList<Integer> artists = users_artists.get(integer);
					for (Integer integer2 : artists) {
						//System.out.println(pairs.getKey() + " , " + integer2);
						writer.write(pairs.getKey() + "\t" + integer2 + "\n");
					}
				}
				count++;
				if(count % 1000 == 0)
					System.out.println(count + "\n");
			}	        
	        
	        //... Close reader and writer.
	        writer.close();  // Close to unlock and flush to disk.        

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		db.closeConnToDB();
	}
	
	public void processPlayedTimes(){

		db.connectToDB();
		Connection conn = db.getConnection();
		PreparedStatement prep = null;
		
		try {
			prep = conn.prepareStatement("SELECT * FROM users2_final;");
			ResultSet r = prep.executeQuery();
			
			HashMap<String, String> users = new HashMap<String, String>(
					10000);
			ArrayList<HashMap<String,Integer>> arr = new ArrayList<HashMap<String,Integer>>();
			for (int i = 0; i < 24; i++) {
				arr.add(new HashMap<String, Integer>(10000));
				
			}
			while (r.next()) {
				users.put(r.getString("string_id"), r.getString("country"));
			}

			File f1 = new File("data/times.tsv");
			if(!f1.exists()){
			  f1.createNewFile();
			}
			else{
				f1.delete();
				f1.createNewFile();
			}
			  
			InputStreamReader isr = new InputStreamReader(new FileInputStream("data/lastfm-dataset-1K/userid-timestamp-artid-artname-traid-traname.tsv"), "UTF-8" );
			BufferedReader reader = new BufferedReader(isr);
	        
	        Writer writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(f1),"UTF8"));
	        

	        //... Loop as long as there are input lines.
	        String line = null;
	        int count = 1;
	        //   0       1        2     3      4      5       6         7      8
	        //userid-timestamp-artid-artname-traid-traname-artist_id-song_id-user_id
	        while ((line=reader.readLine()) != null) {
	        	String[] lineArr = line.split("\t");
	        	
	        	if(lineArr[0] == null || lineArr[0].equals("")){
	        		continue;
	        	}
	        	
	        	String country = users.get(lineArr[0]);
	    		DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
	    		//df.setTimeZone(java.util.TimeZone.getTimeZone("Zulu"));
	    		Date d = null;
	    		try {
	    			d = df.parse(lineArr[1]);
	    		} catch (ParseException e) {
	    			// TODO Auto-generated catch block
	    			e.printStackTrace();
	    			return;
	    		}
	    		
	    		Calendar c = Calendar.getInstance();
	    		c.setTime(d);
	    		
	    		int hour = c.get(Calendar.HOUR_OF_DAY);
	    		if(arr.get(hour).containsKey(country)){
	    			Integer ctr = arr.get(hour).get(country);
	    			arr.get(hour).remove(country);
	    			arr.get(hour).put(country, ++ctr);
	    		}
	    		else{
	    			arr.get(hour).put(country, 1);
	    		}

	    		
	            count++;
	            if(count % 1000 == 0)
	            	System.out.println(count + "\n");
	            
	            if(count % 1000000 == 0){
	            	writer.flush();
	            }
	        }
	        
	        
	        for (int i = 0; i < arr.size(); i++) {
	        	Iterator it = arr.get(i).entrySet().iterator();
				while (it.hasNext()) {
					Map.Entry pairs = (Map.Entry) it.next();
					//System.out.println(pairs.getKey() + " = " + pairs.getValue());
					writer.write(i + "\t" + pairs.getKey() + "\t" + pairs.getValue() + "\n");
				}
				
			}
	        
	        //... Close reader and writer.
	        reader.close();  // Close to unlock.
	        writer.close();  // Close to unlock and flush to disk.        

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		db.closeConnToDB();
	}
	
	public void addArtistIdToSongFile(int start) {
		db.connectToDB();
		Connection conn = db.getConnection();
		PreparedStatement prep = null;
		
		try {
			prep = conn.prepareStatement("SELECT * FROM temp_artists;");
			ResultSet r = prep.executeQuery();
			HashMap<String, Integer> nms = new HashMap<String, Integer>(
					18000000);
			
			HashMap<String, Integer> songs = new HashMap<String, Integer>(
					18000000);
			
			HashMap<String, Integer> users = new HashMap<String, Integer>(
					18000000);
			
			while (r.next()) {
				nms.put(r.getString("mbid"), r.getInt("id"));
			}

			File f1 = new File("data/songs.tsv");
			if(!f1.exists()){
			  f1.createNewFile();
			}
			File f2;
			f2=new File("data/users.tsv");
			if(!f2.exists()){
			  f2.createNewFile();
			}
			  
			InputStreamReader isr = new InputStreamReader(new FileInputStream("data/lastfm-dataset-1K/userid-timestamp-artid-artname-traid-traname.tsv"), "UTF-8" );
			BufferedReader reader = new BufferedReader(isr);
	        
	        //FileWriter writer = new FileWriter("data/artists_songs_users.tsv");
	        //FileWriter writer2 = new FileWriter(f1);
	        //FileWriter writer3 = new FileWriter(f2);
	        Writer writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("data/artists_songs_users.tsv"),"UTF8"));
	        Writer writer2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(f1),"UTF8"));
	        Writer writer3 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(f2),"UTF8"));

	        //... Loop as long as there are input lines.
	        String line = null;
	        int count = 1;
	        int user_count = 1;
	        int song_count = 1;
	        //   0       1        2     3      4      5
	        //userid-timestamp-artid-artname-traid-traname.tsv
	        while ((line=reader.readLine()) != null) {
	        	String[] lineArr = line.split("\t");
	        	
	        	if(lineArr[3] == null || lineArr[0] == null || lineArr[4] == null){
	        		continue;
	        	}
	        	if(!nms.containsKey(lineArr[2]) || lineArr[4] == null || lineArr[4].equals(""))
	        		continue;
	        	int artistID = nms.get(lineArr[2]);
	        	
	        	if(!users.containsKey(lineArr[0])){
	        		users.put(lineArr[0], user_count);
	        		
	        		writer3.write(user_count + "\t" + lineArr[0] +"\n");
	        		
	        		user_count++;
	        	}
	        	
	        	if(!songs.containsKey(lineArr[4])){
	        		songs.put(lineArr[4], song_count);
	        		
	        		writer2.write(song_count + "\t" + lineArr[4] + "\t" + lineArr[5] + "\t" + artistID + "\n");
	        		
	        		song_count++;
	        	}
	        	
	        	
	        	int userID = users.get(lineArr[0]);
	        	int songID = songs.get(lineArr[4]);
	        	
	            writer.write(line + "\t" + artistID + "\t" + songID + "\t" + userID + "\n");
	            
	            count++;
	            if(count % 10000 == 0)
	            	System.out.println(count + "\n");
	            
	            if(count % 1000000 == 0){
	            	writer.flush();
	            }
	        }
	        
	        //... Close reader and writer.
	        reader.close();  // Close to unlock.
	        writer.close();  // Close to unlock and flush to disk.        
	        writer2.close();  // Close to unlock and flush to disk.
	        writer3.close();  // Close to unlock and flush to disk.

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		db.closeConnToDB();
	}
	
	
	public void addArtistIdToArtistsUsers(int start) {
		db.run();
		db.connectToDB();
		Connection conn = db.getConnection();
		PreparedStatement prep = null;
		
		try {
			prep = conn
					.prepareStatement("SELECT * FROM temp_artists WHERE mbid IS NOT NULL ORDER BY mbid;");
			ResultSet r = prep.executeQuery();
			HashMap<String, Integer> nms = new HashMap<String, Integer>(
					18000000);
			while (r.next()) {
				nms.put(r.getString("mbid"), r.getInt("id"));
			}
			Iterator it = nms.entrySet().iterator();
			int count = 0;
			prep = conn
					.prepareStatement("UPDATE artists_users SET artist_id = (?) WHERE artist_mbid = (?);");
			while (it.hasNext()) {
				if(count >= start){
					Map.Entry pairs = (Map.Entry) it.next();
					System.out.println(pairs.getKey() + " = " + pairs.getValue());
					prep.setInt(1, (Integer) pairs.getValue());
					prep.setString(2, (String) pairs.getKey());
					prep.addBatch();
					if (count % 5 == 0) {
						prep.executeBatch();
						prep.clearBatch();
						System.out.println("count " + count);
					}
					
				}
				count++;
			}

			/*
			 * for (int i = start; i <= 17559530; i+=incr) { prep =
			 * conn.prepareStatement("UPDATE artists_users INNER JOIN artists "
			 * + "ON artists_users.artist_mbid = artists.mbid " +
			 * "SET artists_users.artist_id = artists.id " +
			 * "WHERE artists_users.id > (?) AND artists_users.id <= (?) AND artists_users.artist_mbid != '';"
			 * ); prep.setInt(1, i); prep.setInt(2, i + incr); prep.addBatch();
			 * prep.close(); }
			 */

		} catch (SQLException e) {
			e.printStackTrace();
		}
		db.closeConnToDB();
	}

	public void addUserIdToArtistsUsers(int start) {
		db.connectToDB();
		Connection conn = db.getConnection();
		PreparedStatement prep = null;
		try {
			prep = conn
					.prepareStatement("SELECT * FROM users WHERE lastfm_id IS NOT NULL;");
			ResultSet r = prep.executeQuery();
			HashMap<String, Integer> nms = new HashMap<String, Integer>(
					18000000);
			while (r.next()) {
				nms.put(r.getString("lastfm_id"), r.getInt("id"));
			}
			Iterator it = nms.entrySet().iterator();
			int count = 0;
			prep = conn
					.prepareStatement("UPDATE artists_users SET user_id = (?) WHERE user_lastfmid = (?);");
			while (it.hasNext()) {
				if(count >= start){
					Map.Entry pairs = (Map.Entry) it.next();
					System.out.println(pairs.getKey() + " = " + pairs.getValue());
					prep.setInt(1, (Integer) pairs.getValue());
					prep.setString(2, (String) pairs.getKey());
					prep.addBatch();
					if (count % 5 == 0) {
						prep.executeBatch();
						prep.clearBatch();
						System.out.println("count " + count);
					}
					
				}
				
				count++;
			}
			prep.executeBatch();
			prep.clearBatch();

			/*
			 * for (int i = start; i <= 17559530; i+=incr) { prep =
			 * conn.prepareStatement("UPDATE artists_users INNER JOIN artists "
			 * + "ON artists_users.artist_mbid = artists.mbid " +
			 * "SET artists_users.artist_id = artists.id " +
			 * "WHERE artists_users.id > (?) AND artists_users.id <= (?) AND artists_users.artist_mbid != '';"
			 * ); prep.setInt(1, i); prep.setInt(2, i + incr); prep.addBatch();
			 * prep.close(); }
			 */

		} catch (SQLException e) {
			e.printStackTrace();
		}
		db.closeConnToDB();
	}
	
	public static void main(String[] args) {
		DBProcessor p = new DBProcessor();
		if (args.length >= 2) {
			int start = Integer.parseInt(args[1]);
			int end = Integer.parseInt(args[2]);
			switch(Integer.parseInt(args[0])){
				case 1:
					p.addArtistIdToArtistsUsers(start);
					break;
				case 2:
					p.addUserIdToArtistsUsers(start);
					break;
				case 3:
					p.addArtistIdToSongFile(start);
					break;
				case 4:
					p.processPlayedTimes();
					break;
				case 5:
					p.processArtistSuggestions();
					break;
				case 6:
					p.processArtistSuggestions2(start, end);
					break;
				default:
					System.out.println("default case, select 1 or 2");
					break;
					
			}
			
			
		} else {
			System.out.println("Need two cma's.  start int.");
		}
	}
}
